﻿
/*
'===============================================================================
'  MP.LayerHelper.MP_OBS.BL.HrEmployeeManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MP.LayerHelper.MP_OBS.BLL;
using MP.LayerHelper.MP_OBS.DAL;
using MP.LayerHelper.MP_OBS.DAL.EntityClasses;
using MP.LayerHelper.MP_OBS.DAL.FactoryClasses;
using MP.LayerHelper.MP_OBS.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using MP.Core.Library;
using System.Collections;
using System.Collections.Generic;
namespace MP.LayerHelper.MP_OBS.BLL
{
    public class HrEmployeeManager : HrEmployeeManagerBase
    {
        /// <summary>
        /// Purpose: Class constructor.
        /// </summary>
        public HrEmployeeManager()
        {
            // Nothing for now.
        }

        /// <summary>
        /// Method to create an instance of HrEmployeeManager
        /// </summary>
        /// <returns>An instant of HrEmployeeManager class</returns>
        public static HrEmployeeManager CreateInstant()
        {
            return new HrEmployeeManager();
        }
        public DataTable GetByDepart(Guid Depart)
        {
            string strSQL = "select e.Id, e.WindowsUser, e.FullName, e.AgentNumber , (e.WindowsUser + '-' + e.FullName) as Name " +
                            "from (hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and e.IsDeleted=0 and p.IsDeleted=0) " +
                            "inner join hrHumanOrganize as ho on p.OrganizeId=ho.Id and ho.IsDeleted=0 " +
                            "where ho.DepartId=@Depart " +
                            "Order By e.WindowsUser ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@Depart", Depart);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable GetByDepart_Group(Guid Depart, Guid Group)
        {
            string strSQL = "select * " +
                            "from (hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and e.IsDeleted=0 and p.IsDeleted=0) " +
                            "inner join hrHumanOrganize as h on h.Id=p.OrganizeId and h.DepartId=@DepartId " +
                            "where e.GroupId=@GroupId " +
                            "Order By e.WindowsUser ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@DepartId", Depart);
            param[1] = new SqlParameter("@GroupId", Group);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        public DataTable GetByBranch_Depart_Group_AgentNumber(string Branch, string Depart, string Group, string EmployeeId)
        {
            string strSQL = "select e.* " +
                            " from ((hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and e.IsDeleted=0 and p.IsDeleted=0) " +
                            " inner join hrHumanOrganize as h on h.Id=p.OrganizeId) " +
                            " inner join maDepartment as m on m.Id=h.DepartId and m.IsDeleted=0 " +
                            " where 1=1 ";
            if (!string.IsNullOrEmpty(Branch))
                strSQL += " and h.BranchId='" + Branch + "' ";
            if (!string.IsNullOrEmpty(Depart))
                strSQL += " and h.DepartId='" + Depart + "' ";
            if (!string.IsNullOrEmpty(Group))
                strSQL += " and e.GroupId='" + Group + "' ";
            if (!string.IsNullOrEmpty(EmployeeId))
                strSQL += " and e.Id='" + EmployeeId + "' ";
            strSQL += " Order By m.Departname ";

            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }
        public DataTable GetAll()
        {
            string strSQL = "Select e.Id, e.WindowsUser, e.FullName, e.AgentNumber, (e.WindowsUser + '-' + e.FullName) as Name  " +
                            "from hrEmployee e " +
                            "where IsDeleted=0 " +
                            "order by WindowsUser";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, null);
        }
        public DataTable fn_GetAllViewEmployee()
        {
            string strSQL = "Select *  " +
                            "from viewEmployee  ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, null);
        }
        //public DataTable GetByIDCampaign(Guid ID)
        //{
        //    string strSQL = " select em.*,re.CampaignName " +
        //                    " from hrEmployee as em inner join hrRecruitmentCampaign as re on em.CampaignID=re.ID " +
        //                    " where em.ID=@ID ";
        //    SqlParameter[] param = new SqlParameter("@ID",ID);
        //    return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        //}
        public DataTable fn_GetEmployeeByGroupIDNotInSchedule(Guid GroupID, Guid IDExamDetail)
        {
            string strSQL = "spTS_GetEmployeeByGroupIDNotInSchedule";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@GroupID", GroupID);
            param[1] = new SqlParameter("@IDExamDetail", IDExamDetail);
            DataTable result = new DataTable();
            result = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
            return result;
        }

        public HrEmployeeEntity GetByWindowUser(string windowUser)
        {
            EntityCollection<HrEmployeeEntity> items = new EntityCollection<HrEmployeeEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(HrEmployeeFields.WindowsUser == windowUser);
            predicate.Add(HrEmployeeFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }

        public DataTable GetByWindowUserDT(string windowUser)
        {
            string strSQL = "select * from hrEmployee where IsDeleted=0 and WindowsUser=@windowuser";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@windowuser", windowUser);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public HrEmployeeEntity GetByAgentNumber(string AgentNumber)
        {
            EntityCollection<HrEmployeeEntity> items = new EntityCollection<HrEmployeeEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(HrEmployeeFields.AgentNumber == AgentNumber);
            predicate.Add(HrEmployeeFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }
        public DataTable GetTelephonist(Guid Depart)
        {
            string strSQL = "select e.Id, e.WindowsUser, e.FullName, e.AgentNumber , (e.WindowsUser + '-' + e.FullName) as Name " +
                            "from (hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and len(e.AgentNumber)>0  and e.IsDeleted=0 and p.IsDeleted=0) " +
                            "inner join hrHumanOrganize as ho on p.OrganizeId=ho.Id and ho.IsDeleted=0 " +
                            "where ho.DepartId=@Depart " +
                            "Order By e.WindowsUser ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@Depart", Depart);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        public DataTable GetTelephonist2(Guid Depart)
        {
            string strSQL = "select e.Id, e.WindowsUser, e.FullName, e.AgentNumber , (e.WindowsUser + '-' + e.FullName) as Name " +
                            "from (hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and len(e.AgentNumber)>0  and e.IsDeleted=0 and p.IsDeleted=0) " +
                            "inner join hrHumanOrganize as ho on p.OrganizeId=ho.Id and ho.IsDeleted=0 " +
                            "where ho.DepartId=@Depart " +
                            "Order By e.AgentNumber ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@Depart", Depart);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        /// <summary>
        /// lay employee theo ID
        /// </summary>
        /// <param name="ID">ID </param>
        /// <returns></returns>
        public HrEmployeeEntity GetByID(Guid ID)
        {
            EntityCollection<HrEmployeeEntity> items = new EntityCollection<HrEmployeeEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(HrEmployeeFields.Id == ID);
            predicate.Add(HrEmployeeFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }
        public DataTable GetByID_DT(Guid ID)
        {
            string strSQL = " select em.*,ag.AgentGroupName,re.CampaignName " +
                            " from (hrEmployee as em inner join maAgentGroup as ag on em.GroupID=ag.ID) " +
                            " inner join hrRecruitmentCampaign as re on em.CampaignID=re.ID " +
                            " where em.ID=@ID";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@ID", ID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        public DataTable Search(string GroupID, string User, string AgentID, string DepartId, string BranchID)
        {
            string strSQL = "select e.FullName,e.WindowsUser,e.AgentNumber, a.AgentGroupName, m.DepartName " +
                            "from (((hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and p.IsDeleted=0 and e.IsDeleted=0) " +
                            "inner join hrHumanOrganize as h on p.OrganizeId=h.Id and h.IsDeleted=0 and h.BranchId=@BranchID) " +
                            "inner join maAgentGroup as a on e.GroupId=a.Id  and a.IsDeleted=0) " +
                            "inner join maDepartment as m on h.DepartId=m.Id and m.IsDeleted=0 " +
                            "where 1=1 ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@BranchID", new Guid(BranchID));
            if (!string.IsNullOrEmpty(GroupID))
                strSQL += " and e.GroupID='" + new Guid(GroupID) + "'";
            if (!string.IsNullOrEmpty(User))
                strSQL += " and e.WindowsUser='" + User + "'";
            if (!string.IsNullOrEmpty(AgentID))
                strSQL += " and e.AgentNumber='" + AgentID + "'";
            if (new Guid(DepartId) != Guid.Empty)
                strSQL += " and h.DepartId='" + DepartId + "' ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        public HrEmployeeEntity GetByEmployeeID(string emPloyeeID)
        {
            EntityCollection<HrEmployeeEntity> items = new EntityCollection<HrEmployeeEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(HrEmployeeFields.EmployeeId == emPloyeeID);
            predicate.Add(HrEmployeeFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }


        public HrEmployeeEntity GetActiveUserByAgentNumber(string agentNumber, Guid branchID, Guid departID)
        {
            string sql = "SELECT * FROM viewAgentInfo Where IsDeleted=0 AND agentNumber=@agentNumber AND BranchID = @BranchID AND DepartmentID = @DepartID";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@agentNumber", agentNumber);
            param[1] = new SqlParameter("@BranchID", branchID);
            param[2] = new SqlParameter("@DepartID", departID);

            DataTable tbl = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql, param);
            if (tbl != null && tbl.Rows.Count == 1)
            {
                HrEmployeeEntity obj = new HrEmployeeEntity();
                obj.Id = new Guid(tbl.Rows[0]["ID"].ToString());
                return obj;
            }
            return null;
        }

        public HrEmployeeEntity GetActiveUserByWindowsUser(string agentNumber, Guid branchID, Guid departID)
        {
            string sql = "SELECT * FROM viewAgentInfo Where IsDeleted=0 AND WindowsUser=@WindowsUser AND BranchID = @BranchID AND DepartID = @DepartID";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@WindowsUser", agentNumber);
            param[1] = new SqlParameter("@BranchID", branchID);
            param[2] = new SqlParameter("@DepartID", departID);

            DataTable tbl = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql, param);
            if (tbl != null && tbl.Rows.Count == 1)
            {
                HrEmployeeEntity obj = new HrEmployeeEntity();
                obj.Id = new Guid(tbl.Rows[0]["EmployeeID"].ToString());
                return obj;
            }
            return null;
        }

        ///
        // Depart, Agent Group and User/AgentNumber Not Null
        /// <summary>
        /// Depart and Agent Group Not Null
        /// </summary>
        /// <param name="DepartID"></param>
        /// <param name="AgentGroupID"></param>
        /// <param name="User"></param>
        /// <param name="agentlevel"></param>
        /// <param name="agentnumber"></param>
        /// <returns></returns>
        public DataTable SelectEmployee(string DepartID, string AgentGroupID, string User, string agentnumber)
        {
            string strSQL = "select distinct * " +
                            "from  maDepartment as d  " +
                            "inner join maAgentGroup as p on p.DepartmentID = d.ID " +
                            "inner join hrPosition as t on t.DepartID = d.ID and p.DepartmentID= t.DepartID " +
                            "inner join hrEmployee as h on t.EmployeeID = h.ID and h.GroupID = p.ID " +
                            "and t.ID in(select ID from hrPosition where IsDeleted = 0) " +
                            "where p.IsDeleted =0 and d.IsDeleted = 0 and h.IsDeleted = 0 " +
                            "and d.ID = @DepartID ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartID", DepartID);
            if (!string.IsNullOrEmpty(AgentGroupID))
                strSQL += "and p.ID='" + AgentGroupID + "'";
            if (!string.IsNullOrEmpty(User))
                strSQL += " and h.WindowsUser='" + User + "'";
            if (!string.IsNullOrEmpty(agentnumber))
                strSQL += " and h.AgentNumber='" + agentnumber + "'";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        /// <summary>
        /// Depart and User/AgentNumber Not Null
        /// </summary>
        /// <param name="DepartID"></param>
        /// <param name="AgentGroupID"></param>
        /// <param name="User"></param>
        /// <param name="agentnumber"></param>
        /// <returns></returns>
        public DataTable SelectEmployee(string DepartID, string User, string agentnumber)
        {
            string strSQL = "select distinct * " +
                            "from  maDepartment as d  " +
                //"inner join maAgentGroup as p on p.DepartmentID = d.ID " +
                            "inner join hrPosition as t on t.DepartID = d.ID and p.DepartmentID= t.DepartID " +
                            "inner join hrEmployee as h on t.EmployeeID = h.ID " +
                            "and t.ID in(select ID from hrPosition where IsDeleted = 0) " +
                            "where and d.IsDeleted = 0 and h.IsDeleted = 0 " +
                            "and d.ID = @DepartID ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartID", DepartID);
            if (!string.IsNullOrEmpty(User))
                strSQL += " and h.WindowsUser='" + User + "'";
            if (!string.IsNullOrEmpty(agentnumber))
                strSQL += " and h.AgentNumber='" + agentnumber + "'";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        /// <summary>
        /// Depart Not Null With AgentGroup
        /// </summary>
        /// <param name="DepartID"></param>
        /// <param name="AgentGroupID"></param>
        /// <returns></returns>
        public DataTable SelectEmployee(string DepartID)
        {
            string strSQL = "select distinct * " +
                            "from  maDepartment as d  " +
                            "inner join maAgentGroup as p on p.DepartmentID = d.ID " +
                            "inner join hrPosition as t on t.DepartID = d.ID and p.DepartmentID= t.DepartID " +
                            "inner join hrEmployee as h on t.EmployeeID = h.ID and h.GroupID = p.ID " +
                            "and t.ID in(select ID from hrPosition where IsDeleted = 0) " +
                            "where d.IsDeleted = 0 and h.IsDeleted = 0 " +
                            "and d.ID = @DepartID ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartID", DepartID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        /// <summary>
        /// Depart Not Null and Agent Group Not
        /// </summary>
        /// <param name="DepartID"></param>
        /// <returns></returns>
        public DataTable SelectEmployee(string DepartID, string Emtry)
        {
            string strSQL = "select distinct * " +
                            "from  maDepartment as d  " +
                            "inner join hrPosition as t on t.DepartID = d.ID " +
                            "inner join hrEmployee as h on t.EmployeeID = h.ID " +
                            "and EXISTS (select ID from hrPosition where IsDeleted = 0) " +
                            "where d.IsDeleted = 0 and h.IsDeleted = 0 " +
                            "and d.ID = @DepartID ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartID", DepartID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable SelectAllUserByBranch(Guid branchID)
        {
            string sql = @"
                SELECT  E.ID
                        ,(WindowsUser + '-' + FullName) as Name 
                FROM    hrEMPLOYEE E 
                        LEFT JOIN hrPosition P ON (E.ID = P.EmployeeID AND P.IsMaster=1) 
                        INNER JOIN hrHumanOrganize O ON (P.OrganizeID = O.ID)                        
                WHERE E.IsDeleted='0' 
                    AND O.BranchID = '{0}'
                ORDER BY Name";
            sql = String.Format(sql, branchID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql);
        }
        /// <summary>
        /// Lay user thuoc chi nhanh theo dang AgentNumber-FullName
        /// </summary>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public DataTable SelectAllUserByBranch2(Guid branchID)
        {
            string sql = @"
                SELECT  E.ID
                        ,case when len(E.AgentNumber)>0 then E.AgentNumber+'-'+E.FullName else E.WindowsUser+'-'+E.FullName end as name,E.WindowsUser
                FROM    hrEMPLOYEE E 
                        LEFT JOIN hrPosition P ON (E.ID = P.EmployeeID AND P.IsMaster=1) 
                        INNER JOIN hrHumanOrganize O ON (P.OrganizeID = O.ID)                        
                WHERE E.IsDeleted='0' 
                    AND O.BranchID = '{0}'
                ORDER BY Name";
            sql = String.Format(sql, branchID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql);
        }

        /// <summary>
        /// Lay danh sach nhan vien theo phong ban, chi nhanh
        /// </summary>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public DataTable SelectAllUserByDepart_Branch(Guid DepartID, Guid branchID)
        {
            string sql = @"
                SELECT  E.ID
                        ,case when len(E.AgentNumber)>0 then E.AgentNumber+'-'+E.FullName else E.WindowsUser+'-'+E.FullName end as name,E.WindowsUser
                FROM    hrEMPLOYEE E 
                        LEFT JOIN hrPosition P ON (E.ID = P.EmployeeID AND P.IsMaster=1) 
                        INNER JOIN hrHumanOrganize O ON (P.OrganizeID = O.ID)                        
                WHERE E.IsDeleted='0' 
                    AND O.BranchID = '{0}' AND O.DepartID='{1}'
                ORDER BY Name";
            sql = String.Format(sql, branchID, DepartID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql);
        }

        public DataTable SelectAllUserByDepart_Branch2(Guid DepartID, Guid branchID)
        {
            string sql = @"
                SELECT  E.*,ag.AgentGroupName,ag.OrderIndex
                FROM    ((hrEMPLOYEE E 
                        LEFT JOIN hrPosition P ON (E.ID = P.EmployeeID AND P.IsMaster=1))
                        LEFT JOIN hrHumanOrganize O ON (P.OrganizeID = O.ID))
                        LEFT JOIN maAgentGroup ag ON e.GroupId=ag.Id
                WHERE E.IsDeleted='0' 
                    AND O.BranchID = '{0}' AND O.DepartID='{1}'
                ORDER BY E.GroupID";
            sql = String.Format(sql, branchID, DepartID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql);
        }

        /// <summary>
        /// Depart, AgentLevel Not Null
        /// </summary>
        /// <param name="DepartID"></param>
        /// <param name="AgentGroupID"></param>
        /// <param name="User"></param>
        /// <param name="agentnumber"></param>
        /// <returns></returns>
        //public DataTable SelectEmployee(string DepartID, string AgentGroupID, string User, string agentnumber)
        //{
        //    string strSQL = "select distinct * " +
        //                    "hrPosition as t on t.DepartID = d.ID and p.DepartmentID= t.DepartID " +
        //                    "inner join hrEmployee as h on t.EmployeeID = h.ID and h.GroupID = p.ID " +
        //                    "and t.ID in(select ID from hrPosition where IsDeleted = 0) " +
        //                    "where p.IsDeleted =0 and d.IsDeleted = 0 and h.IsDeleted = 0 " +
        //                    "and d.ID = @DepartID ";
        //    SqlParameter[] param = new SqlParameter[1];
        //    param[0] = new SqlParameter("@DepartID", DepartID);
        //    if (!string.IsNullOrEmpty(AgentGroupID))
        //        strSQL += "and p.ID='" + AgentGroupID + "'";
        //    if (!string.IsNullOrEmpty(User))
        //        strSQL += " and h.WindowsUser='" + User + "'";
        //    if (!string.IsNullOrEmpty(agentnumber))
        //        strSQL += " and h.AgentNumber='" + agentnumber + "'";
        //    return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        //}

        public DataTable SelectAgentWithDepartandGroup(string DepartID, string AgentGroupID)
        {
            string strSQL = "select distinct h.*,p.AgentGroupCode,p.AgentGroupName,d.DepartCode " +
                            "from  maAgentGroup as p inner join maDepartment as d on d.ID=p.DepartmentID inner join hrEmployee as h on h.GroupID=p.ID " +
                            "and h.ID in(select Id from hrEmployee where IsDeleted=0) " +
                            "where p.IsDeleted =0 and d.ID = @DepartID and p.ID=@AgentGroupID";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@DepartID", DepartID);
            param[1] = new SqlParameter("@AgentGroupID", AgentGroupID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        public DataTable GetByBranch_Depart(Guid BranchId, Guid DepartId, string listUser)
        {
            listUser = FString.SplitSearchString(listUser);
            string strSQL = "select * " +
                            "from (hrEmployee as e inner join hrPosition as p on e.Id=p.EmployeeId and e.IsDeleted=0 and p.IsDeleted=0) " +
                            "inner join hrHumanOrganize as ho on p.OrganizeId=ho.Id and ho.IsDeleted=0 " +
                            "where 1=1 ";
            if (BranchId != Guid.Empty)
            {
                strSQL += "and ho.DepartId='" + BranchId + "' ";
            }
            if (DepartId != Guid.Empty)
            {
                strSQL += "and ho.BranchId='" + DepartId + "' ";
            }
            if (!string.IsNullOrEmpty(listUser))
                strSQL += " and (e.WindowsUser in (" + listUser + ") or e.AgentNumber in (" + listUser + "))";
            strSQL += " order by e.LastName ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }

        public DataTable Search(string departId, string groupId, string level, string agentOrNumber)
        {
            string strSQL = " SELECT e.ID, e.WindowsUser, e.WorkingStatus, e.FullName, e.AgentNumber, e.HomeAddress, e.CurrentAddress, e.Email, e.MobilePhone, d .DepartName , g.AgentGroupName, l.LevelName " +
                                " FROM hrEmployee e INNER JOIN " +
                                     " hrPosition p ON e.ID = p.EmployeeID INNER JOIN " +
                                     " hrHumanOrganize o ON p.OrganizeId = o.Id LEFT OUTER JOIN " +
                                     " maDepartment d ON o.DepartId = d .ID LEFT OUTER JOIN " +
                                     " maAgentGroup g ON e.GroupID = g.ID LEFT OUTER JOIN " +
                                     " maAgentLevel l ON e.AgentLevel = l.ID " +
                                " WHERE e.IsDeleted = 0 ";
            strSQL += " AND d.ID = '" + departId + "' ";
            if (!string.IsNullOrEmpty(groupId))
            {
                strSQL += " AND g.ID = '" + groupId + "' ";
            }
            if (!string.IsNullOrEmpty(level))
            {
                strSQL += " AND l.ID = '" + level + "' ";
            }
            if (!string.IsNullOrEmpty(agentOrNumber))
            {
                agentOrNumber = FString.SplitSearchString(agentOrNumber);
                strSQL += " AND ( e.WindowsUser IN ( " + agentOrNumber + " ) ";
                strSQL += " OR e.AgentNumber IN ( " + agentOrNumber + " ) ) ";
            }
            strSQL += " ORDER BY e.WindowsUser ASC, e.AgentNumber DESC ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);

        }

        public DataTable GetEmployeeByGroupId(Guid DepartId)
        {
            string strSQL = " SELECT e.*, g.AgentGroupName " +
                            " FROM hrEmployee e " +
                            " INNER JOIN hrPosition p ON e.ID = p.EmployeeID " +
                            " INNER JOIN hrHumanOrganize o ON p.OrganizeID = o.ID " +
                            " LEFT OUTER JOIN maAgentGroup g ON e.GroupID = g.ID " +
                            " WHERE e.IsDeleted = 0 AND o.DepartID = @DepartId " +
                            " AND e.GroupID = ( SELECT ID " +
                                              " FROM maAgentGroup " +
                                              " WHERE IsDeleted = 0 " +
                                              " AND AgentGroupCode LIKE 'GSV%' " +
                                              " AND DepartmentID = @DepartId ) " +
                            " ORDER BY e.WindowsUser ASC ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartId", DepartId.ToString());
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable GetEmployeeTelephone(Guid DepartId)
        {
            string strSQL = " SELECT e.*, g.AgentGroupName " +
                            " FROM hrEmployee e " +
                            " INNER JOIN hrPosition p ON e.ID = p.EmployeeID " +
                            " INNER JOIN hrHumanOrganize o ON p.OrganizeID = o.ID " +
                            " LEFT OUTER JOIN maAgentGroup g ON e.GroupID = g.ID " +
                            " WHERE e.IsDeleted = 0 AND o.DepartID = @DepartId " +
                            " AND e.GroupID IN ( SELECT ID " +
                                              " FROM maAgentGroup " +
                                              " WHERE IsDeleted = 0 " +
                                              " AND ( AgentGroupCode LIKE 'N%' OR AgentGroupCode LIKE 'TT' ) " +
                                              " AND DepartmentID = @DepartId ) " +
                            " ORDER BY e.WindowsUser ASC ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartId", DepartId.ToString());
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }
        public DataTable GetEmployeeTelephone(Guid DepartId, string users, string arr)
        {
            users = FString.SplitSearchString(users);
            arr = FString.SplitSearchString(arr);
            string strSQL = " SELECT e.*, g.AgentGroupName " +
                            " FROM hrEmployee e " +
                            " INNER JOIN hrPosition p ON e.ID = p.EmployeeID " +
                            " INNER JOIN hrHumanOrganize o ON p.OrganizeID = o.ID " +
                            " LEFT OUTER JOIN maAgentGroup g ON e.GroupID = g.ID " +
                            " WHERE e.IsDeleted = 0 AND o.DepartID = @DepartId " +
                            " AND e.GroupID IN ( SELECT ID " +
                                              " FROM maAgentGroup " +
                                              " WHERE IsDeleted = 0 " +
                                              " AND ( AgentGroupCode LIKE 'N%' OR AgentGroupCode LIKE 'TT' ) " +
                                              " AND DepartmentID = @DepartId ) ";
            if (!string.IsNullOrEmpty(users))
            {
                strSQL += " AND ( e.WindowsUser IN ( " + users + " ) " +
                             " OR e.AgentNumber IN ( " + users + " ) " +
                         " ) ";
            }
            if (!string.IsNullOrEmpty(arr))
            {
                strSQL += " AND e.GroupID IN ( " + arr + " ) ";
            }
            strSQL += " ORDER BY e.WindowsUser ASC ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartId", DepartId.ToString());
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable fn_GetListSearchCandidateForSchedule(string _strName, string _agentNumber, string _depart, string _group, string _IDExamDetail)
        {
            _agentNumber = FString.SplitSearchString(_agentNumber);
            string strSQL = "spTS_GetListSearchCandidateForSchedule";
            SqlParameter[] param = new SqlParameter[5];
            param[0] = new SqlParameter("@Name", _strName);
            param[1] = new SqlParameter("@agentNumber", _agentNumber);
            param[2] = new SqlParameter("@group", _group);
            param[3] = new SqlParameter("@IDExamDetail", _IDExamDetail);
            param[4] = new SqlParameter("@depart", _depart);
            DataTable result = new DataTable();
            result = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
            return result;
        }

        public DataTable GetSearchUserQuestionGroup(string userid, string agentnumber, Guid groupid)
        {
            string strSQL = "Select e.Id, e.WindowsUser, e.AgentNumber, e.FullName, g.AgentGroupName " +
                            "From hrEmployee e LEFT JOIN maAgentGroup g ON e.GroupId=g.Id and g.IsDeleted=0 " +
                            "Where e.IsDeleted=0 ";
            string subSQL = "";
            if (!string.IsNullOrEmpty(userid))
            {
                subSQL += " and e.WindowsUser IN (" + FString.SplitSearchString(userid) + ") ";
            }

            if (!string.IsNullOrEmpty(agentnumber))
            {
                subSQL += " and e.AgentNumber IN (" + FString.SplitSearchString(agentnumber) + ") ";
            }

            if (groupid != Guid.Empty)
            {
                subSQL += " and e.GroupId='" + groupid.ToString() + "' ";
            }
            strSQL += subSQL + " Order By e.LastName";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }

        public DataTable GetEmployeeByDepartmentGroup(Guid departId, Guid groupId)
        {
            string strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentGroup";
            Hashtable param = new Hashtable();
            param["departId"] = departId;
            param["groupId"] = groupId;
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.StoredProcedure, strSQL);
        }

        public DataTable GetEmployeeByDepartmentNotGroup(Guid departId, Guid groupId)
        {
            string strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentNotGroup";
            Hashtable param = new Hashtable();
            param["departId"] = departId;
            param["groupId"] = groupId;
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.StoredProcedure, strSQL);
        }

        public DataTable GetEmployeeByDepartmentNotGroup(Guid departId, Guid groupId, int round, int month, int year)
        {
            string strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentNotGroupRoundMonthYear";
            Hashtable param = new Hashtable();
            param["departId"] = departId;
            param["groupId"] = groupId;
            param["round"] = round;
            param["month"] = month;
            param["year"] = year;
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.StoredProcedure, strSQL);
        }
        /// <summary>
        /// get employee by department, not in groupId, in groupIds, 
        /// in strUsers, and not in telephone by department equal 
        /// round-month-year in sqSupervisorTelephone
        /// </summary>
        /// <param name="departId">guid</param>
        /// <param name="groupId">guid</param>
        /// <param name="groupIds">string</param>
        /// <param name="strUsers">string</param>
        /// <param name="round">int</param>
        /// <param name="month">int</param>
        /// <param name="year">int</param>
        /// <returns>datatable</returns>
        public DataTable GetEmployeeByDepartmentGroupsUsersRoundMonthYear(Guid departId, Guid groupId, string groupIds, string strUsers, int round, int month, int year)
        {
            string strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentNotGroupRoundMonthYear";
            Hashtable param = new Hashtable();
            param["departId"] = departId;
            param["groupId"] = groupId;
            param["round"] = round;
            param["month"] = month;
            param["year"] = year;
            if (groupIds != string.Empty)
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentGroupsRoundMonthYear";
                param["groupIds"] = groupIds;
            }
            if (strUsers != string.Empty)
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentUsersRoundMonthYear";
                param["userIds"] = strUsers;
            }
            if (groupIds != string.Empty && strUsers != string.Empty)
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartmentGroupsUsersRoundMonthYear";
                param["groupIds"] = groupIds;
                param["userIds"] = strUsers;
            }
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.StoredProcedure, strSQL);
        }

        public DataTable GetByDeGrsJosUsesRMY(Guid departId, string grs, string jos, string uses, int r, int m, int y)
        {
            int lm = m - 1;
            int ly = y;
            if (lm < 1) ly = y - 1;
            string strSQL = " SELECT * " +
                                ", COALESCE((SELECT Mark FROM sqMediumMark WHERE EmployeeId = ID AND [Month] = '" + lm.ToString() + "' AND [Year] = '" + ly.ToString() + "'), 0) as LastMark " +
                            " FROM viewEmployee " +
                            " WHERE IsDeleted = 0 AND WorkingStatus = 1 " +
                                " AND DepartID = '" + departId.ToString() + "' ";
            if (!string.IsNullOrEmpty(grs))
            {
                grs = FString.SplitSearchString(grs);
                strSQL += " AND GroupId IN (" + grs + ") ";
            }
            if (!string.IsNullOrEmpty(jos))
            {
                jos = FString.SplitSearchString(jos);
                strSQL += " AND JobId IN (" + jos + ") ";
            }
            if (!string.IsNullOrEmpty(uses))
            {
                uses = FString.SplitSearchString(uses);
                strSQL += " AND ( WindowsUser IN (" + uses + ") " +
                                " OR AgentNumber IN (" + uses + ") ) ";
            }
            strSQL += " AND ID NOT IN ( SELECT TelephoneID " +
                          " FROM sqSupervisorTelephone " +
                          " WHERE IsDeleted = 0 " +
                                " 	AND DepartID = '" + departId.ToString() + "' " +
                                " 	AND Round = '" + r.ToString() + "' " +
                                " 	AND Month = '" + m.ToString() + "' " +
                                " 	AND Year = '" + y.ToString() + "' ) ";
            strSQL += " ORDER BY GroupName, WindowsUser ";

            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }

        public DataTable GetEmployeeWithContractByBranchDepartUserIds(Guid branchId, Guid departId, string userIds)
        {
            string strSQL = "spHR_EMPLOYEE_GetEmployee";
            Hashtable param = new Hashtable();
            if (branchId != Guid.Empty)
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByBranch";
                param["branchId"] = branchId;
            }
            if (departId != Guid.Empty)
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByDepart";
                param["departId"] = departId;
            }
            if (!string.IsNullOrEmpty(userIds))
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByUserIds";
                param["userIds"] = userIds;
            }
            if (branchId != Guid.Empty && departId != Guid.Empty)
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByBranchDepart";
                param["branchId"] = branchId;
                param["departId"] = departId;
            }
            if (branchId != Guid.Empty && !string.IsNullOrEmpty(userIds))
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByBranchUserIds";
                param["branchId"] = branchId;
                param["userIds"] = userIds;
            }
            if (departId != Guid.Empty && !string.IsNullOrEmpty(userIds))
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByDepartUserIds";
                param["departId"] = departId;
                param["userIds"] = userIds;
            }
            if (branchId != Guid.Empty && departId != Guid.Empty && !string.IsNullOrEmpty(userIds))
            {
                strSQL = "spHR_EMPLOYEE_GetEmployeeByBranchDepartUserIds";
                param["branchId"] = branchId;
                param["departId"] = departId;
                param["userIds"] = userIds;
            }
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.StoredProcedure, strSQL);
        }

        public DataTable GetByBrDeGrUon(Guid branchId, Guid departId, string groupIds, string jobIds, string uON)
        {
            string strSQL = " SELECT * " +
                            " FROM viewEmployee " +
                            " WHERE IsDeleted = 0 " +
                                " AND BranchID = @branchId ";
            Hashtable param = new Hashtable();
            param["branchId"] = branchId;
            if (departId != Guid.Empty)
            {
                strSQL += " AND DepartID = @departId ";
                param["departId"] = departId;
            }
            if (!string.IsNullOrEmpty(groupIds))
            {
                groupIds = FString.SplitSearchString(groupIds);
                strSQL += " AND GroupID IN (" + groupIds + ") ";
            }
            if (!string.IsNullOrEmpty(jobIds))
            {
                jobIds = FString.SplitSearchString(jobIds);
                strSQL += " AND JobID IN (" + jobIds + ") ";
            }
            if (!string.IsNullOrEmpty(uON))
            {
                uON = FString.SplitSearchString(uON);
                strSQL += " AND ( WindowsUser IN (" + uON + ") " +
                            " OR AgentNumber IN (" + uON + ") ) ";
            }
            strSQL += " ORDER BY DepartID, GroupID, AgentNumber, WindowsUser ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.Text, strSQL);
        }

        public DataTable GetViewEmployee(Guid id)
        {
            string strSQL = " SELECT * " +
                            " FROM viewEmployee " +
                            " WHERE IsDeleted = 0 " +
                                " AND ID = @id ";
            Hashtable param = new Hashtable();
            param["id"] = id;
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.Text, strSQL);
        }

        public DataTable GetByBrDeGrsJobsUonMark(Guid branchId, Guid departId, string groupIds, string jobIds, string uONs, int round, DateTime month)
        {
            string strSQL = " SELECT e.* " +
                                ", COALESCE((SELECT TOP(1) m.Mark FROM sqMediumMark m WHERE m.EmployeeID = e.ID AND m.Month = @month1 AND m.Year = @year1), 0) as LastMark " +
                            " FROM viewEmployee e " +
                            " WHERE e.IsDeleted = 0 " +
                                " AND e.BranchID = @branchId " +
                                " AND e.ID NOT IN ( SELECT st.TelephoneID " +
                                                    " FROM sqSupervisorTelephone st " +
                                                    " WHERE st.IsDeleted = 0 " +
                                                        " AND st.Round = @round " +
                                                        " AND st.Month = @month " +
                                                        " AND st.Year = @year ) ";
            Hashtable param = new Hashtable();
            param["branchId"] = branchId;
            param["round"] = round;
            param["month"] = month.Month;
            param["year"] = month.Year;
            DateTime lastMonth = month.AddMonths(-1);
            param["month1"] = lastMonth.Month;
            param["year1"] = lastMonth.Year;
            if (departId != Guid.Empty)
            {
                strSQL += " AND e.DepartID = @departId ";
                param["departId"] = departId;
            }
            if (!string.IsNullOrEmpty(groupIds))
            {
                groupIds = FString.SplitSearchString(groupIds);
                strSQL += " AND e.GroupID IN (" + groupIds + ") ";
            }
            if (!string.IsNullOrEmpty(jobIds))
            {
                jobIds = FString.SplitSearchString(jobIds);
                strSQL += " AND e.JobID IN (" + jobIds + ") ";
            }
            if (!string.IsNullOrEmpty(uONs))
            {
                uONs = FString.SplitSearchString(uONs);
                strSQL += " AND ( e.AgentNumber IN (" + uONs + ") " +
                            " OR e.WindowsUser IN (" + uONs + ") ) ";
            }
            strSQL += " ORDER BY DepartID, GroupID, AgentNumber, WindowsUser ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.Text, strSQL);
        }

        public void UpdateWindowsUser(string userold, string usernew)
        {
            string strSQL = "Update hrEmployee Set WindowsUser=@usernew Where WindowsUser=@userold";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@userold", userold);
            param[1] = new SqlParameter("@usernew", usernew);
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        /// <summary>
        /// Cập nhật createdby va updatedby cho cac table
        /// </summary>
        /// <param name="userold"></param>
        /// <param name="usernew"></param>
        public void UpdateUserTable(string userold, string usernew)
        {
            string strSQL = @"Select o.[Name] TableName
                              ,c.column_name AS ColumnName
                              ,c.is_nullable AS IsNullable
                              ,c.data_type AS DataType
                              ,c.character_maximum_length AS MaxLength
                            From (sys.objects o INNER JOIN information_schema.columns c ON o.name=c.table_name)
                            Where o.[type]='U' and (c.column_name like 'create%' or c.column_name like 'update%') and c.column_name like '%by'
                            Order By o.[Name]";
            DataTable tblTable = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
            if (tblTable != null && tblTable.Rows.Count > 0)
            {
                string fstrSQL = "Update {0} " +
                                 "Set {1}=@usernew " +
                                 "Where {1}=@userold ";
                SqlParameter[] param = new SqlParameter[2];

                param[0] = new SqlParameter("@userold", userold);
                param[1] = new SqlParameter("@usernew", usernew);
                strSQL = "";
                foreach (DataRow r in tblTable.Rows)
                {
                    if (r["DataType"].ToString() == "varchar" || r["DataType"].ToString() == "nvarchar")
                    {
                        strSQL += string.Format(fstrSQL, r["TableName"].ToString(), r["ColumnName"].ToString()) + " \r\n";
                    }
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
            }
        }
        public DataTable getAllUser()
        {
            string strSQL = "select * from vw_user where IsDeleted=0";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }
        public DataTable SearchUserInAccount(string username, string fullname, string phonenumber,string email, string status, string belongaccount)
        {
            string strSQL = "UserInAccountSearch";
            SqlParameter[] param=new SqlParameter[6];
            param[0] = new SqlParameter("@username", username);
            param[1] = new SqlParameter("@fullname", fullname);
            param[2] = new SqlParameter("@phonenumber", phonenumber);
            param[3] = new SqlParameter("@email", email);
            param[4] = new SqlParameter("@status", status);
            param[5] = new SqlParameter("@belongaccount", belongaccount);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }
    }
}
